import pymysql.cursors
import numpy as np
import pandas as pd

class Db:
    def __init__(self):
        self.connection = pymysql.Connect(host='192.168.19.101', port=3306, db='bookbrrow', user='root', passwd='123456',
                                          charset='utf8')
    def get_DBdata(self):
        fetch_sql = "select * from bookcords;"
        dg = pd.read_sql(fetch_sql, self.connection)  # 利用pd库 将sql 和连接放入执行sql
        column_list = list(dg.columns)  # 得到查询字段名集合
        rows = np.array(dg)  # 利用np库将得到结果变成数组array
        data_list = []
        for row in rows:
            rowdatas =list(row)
            print(rowdatas)
            rowdatas = [  str(rowdata).replace('\n','').replace('\r','') for rowdata in rowdatas ]
            data_list.append(rowdatas)
            # 本来数据和字段名是分开的，但是json格式需要key value，一个字段名组合一个值
            # 多个字段名和值的组合 组成要给对象，多个对象追加到集合中
        bookdataFrame = pd.DataFrame(data_list)
        keys = list(range(len(column_list)))
        headerdict = dict(zip(keys, column_list))
        bookdataFrame.reset_index
        bookdataFrame.rename(columns=headerdict, inplace=True)
        return bookdataFrame
db = Db()
data_list = db.get_DBdata()
print(data_list)